'''
功能：根据xlsx模型生成建表语句
'''
import pandas as pd

from setting import (
    XLSX_PATH, 
    OUTPUT_CREATE_DIR,
    TIMEOUTS,
    OUTPUT_TABLE_FILENAME,
    SHEET_NAME
)

print(TIMEOUTS)
#1.0参数设置
#读取文件路径
file_path=XLSX_PATH
#数据路径
out_path=OUTPUT_CREATE_DIR
#输出文件名称
outFileName=OUTPUT_TABLE_FILENAME
#hdfs路径
location_path=r'/tenants/dtsw_jx/wireless/dtsw_pro/dtsw_base/5gpm/dm'
#分区名称
parttion_field=['p_provincecode','p_date','p_hour','p_month','p_week','p_year','p_quarter']
#数据库名称
database_name='dtsw_pro'
#存储类型
stored_type='parquet'
#是空数组，则读取所有sheet
sheet_name=SHEET_NAME


# 1.1 指定全局参数
tables_dict={} # 存储所有表信息
# create_sql模板
create_sql='''drop table if exists {database}.{table_name};
create external table {database}.{table_name} (
    {fields} 
) partitioned by (
    {partitions} 
) stored as {stored_type}
 location '{location_path}/{table_name}'
TBLPROPERTIES (
  'parquet.compression'='SNAPPY'
);
'''
# 2.0处理sheet 数据 ，not 不是空列表
if sheet_name is None or not sheet_name:
     print('do not  specify  sheet!')
     df=pd.read_excel(file_path,sheet_name=None,header=0)
else:
    print(f'指定读取sheet是：{sheet_name}')
    df=pd.read_excel(file_path,sheet_name=sheet_name,header=0)

for sheet_name,data in df.items():
    #打标是否是pdm字段
    is_pdm_field=False
    # 是否是表名
    is_table_name=False
    # 存储字段、字段类型、中文名、
    filed_name_type={}
    #英文表名
    table_name=str()
    #中文表名
    ch_table_name=str()
    #分区字段
    p_name={}
    for index,row in data.iterrows(): # 行索引、行数据
        # print(f'{row.iloc[0]} {row.iloc[1]} {row.iloc[2]}')
        i=0 # row得字段顺序
        for filed in row:
            if filed=='目标表英文名':
               print(f"编号：{i},字段：{filed},行号：{index}")
               i=i+1
            pass 
        if(row.iloc[0]=='PDM字段英文名'):
            is_pdm_field=True
            continue
        if(row.iloc[1]=='目标表英文名'):
            is_table_name=True
            continue
        if(is_table_name) and (len(table_name)==0):
            table_name=row.iloc[1] # 表名
            ch_table_name=row.iloc[2] # 中文表名
        if(is_pdm_field) and (row.iloc[0] in parttion_field):
            p_name[index]=row.iloc[0]+','+row.iloc[1] # 分区字段名
        if(is_pdm_field) and (row.iloc[0] not in parttion_field):
            filed_name_type[index]=row.iloc[0]+","+row.iloc[1]+","+row.iloc[2]
    # 遍历字段
    str_list=[]
    for key in sorted(filed_name_type.keys(),reverse=False): 
        str_list.append(filed_name_type[key].split(',')[0]+' '+filed_name_type[key].split(',')[1]+' comment \''+ filed_name_type[key].split(',')[2]+'\'')
    str_filed='\n    ,'.join(str_list)
    # 遍历分区字段
    p_name_list=[]
    for index in p_name.keys():
        p_name_list.append(p_name[index].split(',')[0]+'  '+p_name[index].split(',')[1])
    str_p_name='\n    ,'.join(p_name_list)
    location_file=location_path+table_name
    tables_dict[table_name]=create_sql.format_map({'database':database_name,'table_name':table_name,'fields':str_filed,'partitions':str_p_name,'stored_type':stored_type,'location_path':location_path})
# 3.0 输出sheet 数据
for table_name,sql in tables_dict.items():
    #覆盖增量输出
    with open(out_path+'\\'+outFileName,'w') as f:
        f.write(sql)
        f.close()